1 package org.apache.solr.handler.dataimport;
2
3 import junit.framework.Assert;
4 import org.apache.solr.common.util.SuppressForbidden;
5 import org.junit.After;
6 import org.junit.Before;
7 import org.slf4j.Logger;
8 import org.slf4j.LoggerFactory;
9
10 import java.io.File;
11 import java.lang.invoke.MethodHandles;
12 import java.nio.file.Files;
13 import java.sql.Connection;
14 import java.sql.PreparedStatement;
15 import java.sql.ResultSet;
16 import java.sql.SQLException;
17 import java.sql.Statement;
18 import java.sql.Timestamp;
19 import java.text.SimpleDateFormat;
20 import java.util.ArrayList;
21 import java.util.HashMap;
22 import java.util.HashSet;
23 import java.util.List;
24 import java.util.Locale;
25 import java.util.Map;
26 import java.util.Set;
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45 public abstract class AbstractSqlEntityProcessorTestCase extends
46 AbstractDIHJdbcTestCase {
47
48 private static final Logger log = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
49
50 protected boolean underlyingDataModified;
51 protected boolean useSimpleCaches;
52 protected boolean countryEntity;
53 protected boolean countryCached;
54 protected boolean countryZipper;
55 protected boolean sportsEntity;
56 protected boolean sportsCached;
57 protected boolean sportsZipper;
58
59 protected boolean wrongPeopleOrder ;
60 protected boolean wrongSportsOrder ;
61 protected boolean wrongCountryOrder;
62
63 protected String rootTransformerName;
64 protected boolean countryTransformer;
65 protected boolean sportsTransformer;
66 protected String fileLocation;
67 protected String fileName;
68
69 @Before
70 public void beforeSqlEntitiyProcessorTestCase() throws Exception {
71 File tmpdir = createTempDir().toFile();
72 fileLocation = tmpdir.getPath();
73 fileName = "the.properties";
74 }
75
76 @After
77 public void afterSqlEntitiyProcessorTestCase() throws Exception {
78 useSimpleCaches = false;
79 countryEntity = false;
80 countryCached = false;
81 countryZipper = false;
82 sportsEntity = false;
83 sportsCached = false;
84 sportsZipper = false;
85
86 wrongPeopleOrder = false;
87 wrongSportsOrder = false;
88 wrongCountryOrder= false;
89
90 rootTransformerName = null;
91 countryTransformer = false;
92 sportsTransformer = false;
93 underlyingDataModified = false;
94
95
96
97 if(fileLocation!=null) {
98 Files.deleteIfExists(new File(fileLocation + File.separatorChar + fileName).toPath());
99 Files.deleteIfExists(new File(fileLocation).toPath());
100 }
101 }
102
103 protected void logPropertiesFile() {
104 Map<String,String> init = new HashMap<>();
105 init.put("filename", fileName);
106 init.put("directory", fileLocation);
107 SimplePropertiesWriter spw = new SimplePropertiesWriter();
108 spw.init(new DataImporter(), init);
109 Map<String,Object> props = spw.readIndexerProperties();
110 if(props!=null) {
111 StringBuilder sb = new StringBuilder();
112 sb.append("\ndataimporter.properties: \n");
113 for(Map.Entry<String,Object> entry : props.entrySet()) {
114 sb.append(" > key=" + entry.getKey() + " / value=" + entry.getValue() + "\n");
115 }
116 log.debug(sb.toString());
117 }
118 }
119
120 protected abstract String deltaQueriesCountryTable();
121
122 protected abstract String deltaQueriesPersonTable();
123
124 protected void singleEntity(int numToExpect) throws Exception {
125 h.query("/dataimport", generateRequest());
126 assertQ("There should be 1 document per person in the database: "
127 + totalPeople(), req("*:*"), "//*[@numFound='" + totalPeople() + "']");
128 Assert.assertTrue("Expecting " + numToExpect
129 + " database calls, but DIH reported " + totalDatabaseRequests(),
130 totalDatabaseRequests() == numToExpect);
131 }
132
133 protected void simpleTransform(int numToExpect) throws Exception {
134 rootTransformerName = "AddAColumnTransformer";
135 h.query("/dataimport", generateRequest());
136 assertQ(
137 "There should be 1 document with a transformer-added column per person is the database: "
138 + totalPeople(), req("AddAColumn_s:Added"), "//*[@numFound='"
139 + totalPeople() + "']");
140 Assert.assertTrue("Expecting " + numToExpect
141 + " database calls, but DIH reported " + totalDatabaseRequests(),
142 totalDatabaseRequests() == numToExpect);
143 }
144
145
146
147
148
149
150 protected void complexTransform(int numToExpect, int numDeleted)
151 throws Exception {
152 rootTransformerName = "TripleThreatTransformer";
153 h.query("/dataimport", generateRequest());
154 int totalDocs = ((totalPeople() * 3) + (numDeleted * 2));
155 int totalAddedDocs = (totalPeople() + numDeleted);
156 assertQ(
157 req("q", "*:*", "rows", "" + (totalPeople() * 3), "sort", "id asc"),
158 "//*[@numFound='" + totalDocs + "']");
159 assertQ(req("id:TripleThreat-1-*"), "//*[@numFound='" + totalAddedDocs
160 + "']");
161 assertQ(req("id:TripleThreat-2-*"), "//*[@numFound='" + totalAddedDocs
162 + "']");
163 if (personNameExists("Michael") && countryCodeExists("NR")) {
164 assertQ(
165 "Michael and NR are assured to be in the database. Therefore the transformer should have added leahciM and RN on the same document as id:TripleThreat-1-3",
166 req("+id:TripleThreat-1-3 +NAME_mult_s:Michael +NAME_mult_s:leahciM +COUNTRY_CODES_mult_s:NR +COUNTRY_CODES_mult_s:RN"),
167 "//*[@numFound='1']");
168 }
169 assertQ(req("AddAColumn_s:Added"), "//*[@numFound='" + totalAddedDocs
170 + "']");
171 Assert.assertTrue("Expecting " + numToExpect
172 + " database calls, but DIH reported " + totalDatabaseRequests(),
173 totalDatabaseRequests() == numToExpect);
174 }
175
176 protected void withChildEntities(boolean cached, boolean checkDatabaseRequests)
177 throws Exception {
178 rootTransformerName = random().nextBoolean() ? null
179 : "AddAColumnTransformer";
180 int numChildren = random().nextInt(1) + 1;
181 int numDatabaseRequests = 1;
182 if (underlyingDataModified) {
183 if (countryEntity) {
184 if (cached) {
185 numDatabaseRequests++;
186 } else {
187 numDatabaseRequests += totalPeople();
188 }
189 }
190 if (sportsEntity) {
191 if (cached) {
192 numDatabaseRequests++;
193 } else {
194 numDatabaseRequests += totalPeople();
195 }
196 }
197 } else {
198 countryEntity = true;
199 sportsEntity = true;
200 if(countryZipper||sportsZipper){
201 countryEntity = countryZipper;
202 sportsEntity = sportsZipper;
203 }else{
204 if (numChildren == 1) {
205 countryEntity = random().nextBoolean();
206 sportsEntity = !countryEntity;
207 }
208 }
209 if (countryEntity) {
210 countryTransformer = random().nextBoolean();
211 if (cached) {
212 numDatabaseRequests++;
213 countryCached = true;
214 } else {
215 numDatabaseRequests += totalPeople();
216 }
217 }
218 if (sportsEntity) {
219 sportsTransformer = random().nextBoolean();
220 if (cached) {
221 numDatabaseRequests++;
222 sportsCached = true;
223 } else {
224 numDatabaseRequests += totalPeople();
225 }
226 }
227 }
228 h.query("/dataimport", generateRequest());
229
230 assertQ("There should be 1 document per person in the database: "
231 + totalPeople(), req("*:*"), "//*[@numFound='" + (totalPeople()) + "']");
232 if (!underlyingDataModified
233 && "AddAColumnTransformer".equals(rootTransformerName)) {
234 assertQ(
235 "There should be 1 document with a transformer-added column per person is the database: "
236 + totalPeople(), req("AddAColumn_s:Added"), "//*[@numFound='"
237 + (totalPeople()) + "']");
238 }
239 if (countryEntity) {
240 {
241 String[] people = getStringsFromQuery("SELECT NAME FROM PEOPLE WHERE DELETED != 'Y'");
242 String man = people[random().nextInt(people.length)];
243 String[] countryNames = getStringsFromQuery("SELECT C.COUNTRY_NAME FROM PEOPLE P "
244 + "INNER JOIN COUNTRIES C ON P.COUNTRY_CODE=C.CODE "
245 + "WHERE P.DELETED!='Y' AND C.DELETED!='Y' AND P.NAME='" + man + "'");
246
247 assertQ(req("{!term f=NAME_mult_s}"+ man), "//*[@numFound='1']",
248 countryNames.length>0?
249 "//doc/str[@name='COUNTRY_NAME_s']='" + countryNames[random().nextInt(countryNames.length)] + "'"
250 :"//doc[count(*[@name='COUNTRY_NAME_s'])=0]");
251 }
252 {
253 String[] countryCodes = getStringsFromQuery("SELECT CODE FROM COUNTRIES WHERE DELETED != 'Y'");
254 String theCode = countryCodes[random().nextInt(countryCodes.length)];
255 int num = numberPeopleByCountryCode(theCode);
256 if(num>0){
257 String nrName = countryNameByCode(theCode);
258 assertQ(req("COUNTRY_CODES_mult_s:"+theCode), "//*[@numFound='" + num + "']",
259 "//doc/str[@name='COUNTRY_NAME_s']='" + nrName + "'");
260 }else{
261 assertQ(req("COUNTRY_CODES_mult_s:"+theCode), "//*[@numFound='" + num + "']");
262 }
263 }
264 if (countryTransformer && !underlyingDataModified) {
265 assertQ(req("countryAdded_s:country_added"), "//*[@numFound='"
266 + totalPeople() + "']");
267 }
268 }
269 if (sportsEntity) {
270 if (!underlyingDataModified) {
271 assertQ(req("SPORT_NAME_mult_s:Sailing"), "//*[@numFound='2']");
272 }
273 String [] names = getStringsFromQuery("SELECT NAME FROM PEOPLE WHERE DELETED != 'Y'");
274 String name = names[random().nextInt(names.length)];
275 int personId = getIntFromQuery("SELECT ID FROM PEOPLE WHERE DELETED != 'Y' AND NAME='"+name+"'");
276 String[] michaelsSports = sportNamesByPersonId(personId);
277
278 String[] xpath = new String[michaelsSports.length + 1];
279 xpath[0] = "//*[@numFound='1']";
280 int i = 1;
281 for (String ms : michaelsSports) {
282 xpath[i] = "
283 + ms + "'";
284 i++;
285 }
286 assertQ(req("NAME_mult_s:" + name.replaceAll("\\W", "\\\\$0")),
287 xpath);
288 if (!underlyingDataModified && sportsTransformer) {
289 assertQ(req("sportsAdded_s:sport_added"), "//*[@numFound='"
290 + (totalSportsmen()) + "']");
291 }
292 assertQ("checking orphan sport is absent",
293 req("{!term f=SPORT_NAME_mult_s}No Fishing"), "//*[@numFound='0']");
294 }
295 if (checkDatabaseRequests) {
296 Assert.assertTrue("Expecting " + numDatabaseRequests
297 + " database calls, but DIH reported " + totalDatabaseRequests(),
298 totalDatabaseRequests() == numDatabaseRequests);
299 }
300 }
301
302 protected void simpleCacheChildEntities(boolean checkDatabaseRequests)
303 throws Exception {
304 useSimpleCaches = true;
305 countryEntity = true;
306 sportsEntity = true;
307 countryCached = true;
308 sportsCached = true;
309 int dbRequestsMoreThan = 3;
310 int dbRequestsLessThan = totalPeople() * 2 + 1;
311 h.query("/dataimport", generateRequest());
312 assertQ(req("*:*"), "//*[@numFound='" + (totalPeople()) + "']");
313 if (!underlyingDataModified
314 || (personNameExists("Samantha") && "Nauru"
315 .equals(countryNameByCode("NR")))) {
316 assertQ(req("NAME_mult_s:Samantha"), "//*[@numFound='1']",
317 "//doc/str[@name='COUNTRY_NAME_s']='Nauru'");
318 }
319 if (!underlyingDataModified) {
320 assertQ(req("COUNTRY_CODES_mult_s:NR"), "//*[@numFound='2']",
321 "//doc/str[@name='COUNTRY_NAME_s']='Nauru'");
322 assertQ(req("SPORT_NAME_mult_s:Sailing"), "//*[@numFound='2']");
323 }
324 String[] michaelsSports = sportNamesByPersonId(3);
325 if (!underlyingDataModified || michaelsSports.length > 0) {
326 String[] xpath = new String[michaelsSports.length + 1];
327 xpath[0] = "//*[@numFound='1']";
328 int i = 1;
329 for (String ms : michaelsSports) {
330 xpath[i] = "//doc/arr[@name='SPORT_NAME_mult_s']/str[" + i + "]='" + ms
331 + "'";
332 i++;
333 }
334 assertQ(req("NAME_mult_s:Michael"), xpath);
335 }
336 if (checkDatabaseRequests) {
337 Assert.assertTrue("Expecting more than " + dbRequestsMoreThan
338 + " database calls, but DIH reported " + totalDatabaseRequests(),
339 totalDatabaseRequests() > dbRequestsMoreThan);
340 Assert.assertTrue("Expecting fewer than " + dbRequestsLessThan
341 + " database calls, but DIH reported " + totalDatabaseRequests(),
342 totalDatabaseRequests() < dbRequestsLessThan);
343 }
344 }
345
346
347 private int getIntFromQuery(String query) throws Exception {
348 Connection conn = null;
349 Statement s = null;
350 ResultSet rs = null;
351 try {
352 conn = newConnection();
353 s = conn.createStatement();
354 rs = s.executeQuery(query);
355 if (rs.next()) {
356 return rs.getInt(1);
357 }
358 return 0;
359 } catch (SQLException e) {
360 throw e;
361 } finally {
362 try {
363 rs.close();
364 } catch (Exception ex) {}
365 try {
366 s.close();
367 } catch (Exception ex) {}
368 try {
369 conn.close();
370 } catch (Exception ex) {}
371 }
372 }
373
374 private String[] getStringsFromQuery(String query) throws Exception {
375 Connection conn = null;
376 Statement s = null;
377 ResultSet rs = null;
378 try {
379 conn = newConnection();
380 s = conn.createStatement();
381 rs = s.executeQuery(query);
382 List<String> results = new ArrayList<>();
383 while (rs.next()) {
384 results.add(rs.getString(1));
385 }
386 return results.toArray(new String[results.size()]);
387 } catch (SQLException e) {
388 throw e;
389 } finally {
390 try {
391 rs.close();
392 } catch (Exception ex) {}
393 try {
394 s.close();
395 } catch (Exception ex) {}
396 try {
397 conn.close();
398 } catch (Exception ex) {}
399 }
400 }
401
402 public int totalCountries() throws Exception {
403 return getIntFromQuery("SELECT COUNT(1) FROM COUNTRIES WHERE DELETED != 'Y' ");
404 }
405
406 public int totalPeople() throws Exception {
407 return getIntFromQuery("SELECT COUNT(1) FROM PEOPLE WHERE DELETED != 'Y' ");
408 }
409
410 public int totalSportsmen() throws Exception {
411 return getIntFromQuery("SELECT COUNT(*) FROM PEOPLE WHERE "
412 + "EXISTS(SELECT ID FROM PEOPLE_SPORTS WHERE PERSON_ID=PEOPLE.ID AND PEOPLE_SPORTS.DELETED != 'Y')"
413 + " AND PEOPLE.DELETED != 'Y'");
414 }
415
416 public boolean countryCodeExists(String cc) throws Exception {
417 return getIntFromQuery("SELECT COUNT(1) country_name FROM COUNTRIES WHERE DELETED != 'Y' AND CODE='"
418 + cc + "'") > 0;
419 }
420
421 public String countryNameByCode(String cc) throws Exception {
422 String[] s = getStringsFromQuery("SELECT country_name FROM COUNTRIES WHERE DELETED != 'Y' AND CODE='"
423 + cc + "'");
424 return s.length == 0 ? null : s[0];
425 }
426
427 public int numberPeopleByCountryCode(String cc) throws Exception {
428 return getIntFromQuery("Select count(1) " + "from people p "
429 + "inner join countries c on p.country_code=c.code "
430 + "where p.deleted!='Y' and c.deleted!='Y' and c.code='" + cc + "'");
431 }
432
433 public String[] sportNamesByPersonId(int personId) throws Exception {
434 return getStringsFromQuery("SELECT ps.SPORT_NAME "
435 + "FROM people_sports ps "
436 + "INNER JOIN PEOPLE p ON p.id = ps.person_Id "
437 + "WHERE ps.DELETED != 'Y' AND p.DELETED != 'Y' " + "AND ps.person_id="
438 + personId + " " + "ORDER BY ps.id");
439 }
440
441 public boolean personNameExists(String pn) throws Exception {
442 return getIntFromQuery("SELECT COUNT(1) FROM PEOPLE WHERE DELETED != 'Y' AND NAME='"
443 + pn + "'") > 0;
444 }
445
446 public String personNameById(int id) throws Exception {
447 String[] nameArr = getStringsFromQuery("SELECT NAME FROM PEOPLE WHERE ID="
448 + id);
449 if (nameArr.length == 0) {
450 return null;
451 }
452 return nameArr[0];
453 }
454
455 @SuppressForbidden(reason = "Needs currentTimeMillis to set change time for SQL query")
456 public IntChanges modifySomePeople() throws Exception {
457 underlyingDataModified = true;
458 int numberToChange = random().nextInt(people.length + 1);
459 Set<Integer> changeSet = new HashSet<>();
460 Set<Integer> deleteSet = new HashSet<>();
461 Set<Integer> addSet = new HashSet<>();
462 Connection conn = null;
463 PreparedStatement change = null;
464 PreparedStatement delete = null;
465 PreparedStatement add = null;
466
467
468 Timestamp theTime = new Timestamp(System.currentTimeMillis() + 1000);
469 log.debug("PEOPLE UPDATE USING TIMESTAMP: "
470 + new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ", Locale.ROOT)
471 .format(theTime));
472 try {
473 conn = newConnection();
474 change = conn
475 .prepareStatement("update people set name=?, last_modified=? where id=?");
476 delete = conn
477 .prepareStatement("update people set deleted='Y', last_modified=? where id=?");
478 add = conn
479 .prepareStatement("insert into people (id,name,country_code,last_modified) values (?,?,'ZZ',?)");
480 for (int i = 0; i < numberToChange; i++) {
481 int tryIndex = random().nextInt(people.length);
482 Integer id = (Integer) people[tryIndex][0];
483 if (!changeSet.contains(id) && !deleteSet.contains(id)) {
484 boolean changeDontDelete = random().nextBoolean();
485 if (changeDontDelete) {
486 changeSet.add(id);
487 change.setString(1, "MODIFIED " + people[tryIndex][1]);
488 change.setTimestamp(2, theTime);
489 change.setInt(3, id);
490 Assert.assertEquals(1, change.executeUpdate());
491 } else {
492 deleteSet.add(id);
493 delete.setTimestamp(1, theTime);
494 delete.setInt(2, id);
495 Assert.assertEquals(1, delete.executeUpdate());
496 }
497 }
498 }
499 int numberToAdd = random().nextInt(3);
500 for (int i = 0; i < numberToAdd; i++) {
501 int tryIndex = random().nextInt(people.length);
502 Integer id = (Integer) people[tryIndex][0];
503 Integer newId = id + 1000;
504 String newDesc = "ADDED " + people[tryIndex][1];
505 if (!addSet.contains(newId)) {
506 addSet.add(newId);
507 add.setInt(1, newId);
508 add.setString(2, newDesc);
509 add.setTimestamp(3, theTime);
510 Assert.assertEquals(1, add.executeUpdate());
511 }
512 }
513 conn.commit();
514 } catch (SQLException e) {
515 throw e;
516 } finally {
517 try {
518 change.close();
519 } catch (Exception ex) {}
520 try {
521 conn.close();
522 } catch (Exception ex) {}
523 }
524 IntChanges c = new IntChanges();
525 c.changedKeys = changeSet.toArray(new Integer[changeSet.size()]);
526 c.deletedKeys = deleteSet.toArray(new Integer[deleteSet.size()]);
527 c.addedKeys = addSet.toArray(new Integer[addSet.size()]);
528 return c;
529 }
530
531 @SuppressForbidden(reason = "Needs currentTimeMillis to set change time for SQL query")
532 public String[] modifySomeCountries() throws Exception {
533 underlyingDataModified = true;
534 int numberToChange = random().nextInt(countries.length + 1);
535 Set<String> changeSet = new HashSet<>();
536 Connection conn = null;
537 PreparedStatement change = null;
538
539
540 Timestamp theTime = new Timestamp(System.currentTimeMillis() + 1000);
541 log.debug("COUNTRY UPDATE USING TIMESTAMP: "
542 + new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ", Locale.ROOT)
543 .format(theTime));
544 try {
545 conn = newConnection();
546 change = conn
547 .prepareStatement("update countries set country_name=?, last_modified=? where code=?");
548 for (int i = 0; i < numberToChange; i++) {
549 int tryIndex = random().nextInt(countries.length);
550 String code = countries[tryIndex][0];
551 if (!changeSet.contains(code)) {
552 changeSet.add(code);
553 change.setString(1, "MODIFIED " + countries[tryIndex][1]);
554 change.setTimestamp(2, theTime);
555 change.setString(3, code);
556 Assert.assertEquals(1, change.executeUpdate());
557
558 }
559 }
560 } catch (SQLException e) {
561 throw e;
562 } finally {
563 try {
564 change.close();
565 } catch (Exception ex) {}
566 try {
567 conn.close();
568 } catch (Exception ex) {}
569 }
570 return changeSet.toArray(new String[changeSet.size()]);
571 }
572
573 class IntChanges {
574 public Integer[] changedKeys;
575 public Integer[] deletedKeys;
576 public Integer[] addedKeys;
577
578 @Override
579 public String toString() {
580 StringBuilder sb = new StringBuilder();
581 if(changedKeys!=null) {
582 sb.append("changes: ");
583 for(int i : changedKeys) {
584 sb.append(i).append(" ");
585 }
586 }
587 if(deletedKeys!=null) {
588 sb.append("deletes: ");
589 for(int i : deletedKeys) {
590 sb.append(i).append(" ");
591 }
592 }
593 if(addedKeys!=null) {
594 sb.append("adds: ");
595 for(int i : addedKeys) {
596 sb.append(i).append(" ");
597 }
598 }
599 return sb.toString();
600 }
601 }
602
603 @Override
604 protected String generateConfig() {
605 String ds = null;
606 if (dbToUse == Database.DERBY) {
607 ds = "derby";
608 } else if (dbToUse == Database.HSQLDB) {
609 ds = "hsqldb";
610 } else {
611 throw new AssertionError("Invalid database to use: " + dbToUse);
612 }
613 StringBuilder sb = new StringBuilder();
614 sb.append("\n<dataConfig> \n");
615 sb.append("<propertyWriter type=''SimplePropertiesWriter'' directory=''" + fileLocation + "'' filename=''" + fileName + "'' />\n");
616 sb.append("<dataSource name=''hsqldb'' driver=''org.hsqldb.jdbcDriver'' url=''jdbc:hsqldb:mem:.'' /> \n");
617 sb.append("<dataSource name=''derby'' driver=''org.apache.derby.jdbc.EmbeddedDriver'' url=''jdbc:derby:memory:derbyDB;territory=en_US'' /> \n");
618 sb.append("<document name=''TestSqlEntityProcessor''> \n");
619 sb.append("<entity name=''People'' ");
620 sb.append("pk=''" + (random().nextBoolean() ? "ID" : "People.ID") + "'' ");
621 sb.append("processor=''SqlEntityProcessor'' ");
622 sb.append("dataSource=''" + ds + "'' ");
623 sb.append(rootTransformerName != null ? "transformer=''"
624 + rootTransformerName + "'' " : "");
625
626 sb.append("query=''SELECT ID, NAME, COUNTRY_CODE FROM PEOPLE WHERE DELETED != 'Y' "
627 +((sportsZipper||countryZipper?"ORDER BY ID":"")
628 +(wrongPeopleOrder? " DESC":""))+"'' ");
629
630 sb.append(deltaQueriesPersonTable());
631 sb.append("> \n");
632
633 sb.append("<field column=''NAME'' name=''NAME_mult_s'' /> \n");
634 sb.append("<field column=''COUNTRY_CODE'' name=''COUNTRY_CODES_mult_s'' /> \n");
635
636 if (countryEntity) {
637 sb.append("<entity name=''Countries'' ");
638 sb.append("pk=''" + (random().nextBoolean() ? "CODE" : "Countries.CODE")
639 + "'' ");
640 sb.append("dataSource=''" + ds + "'' ");
641 sb.append(countryTransformer ? "transformer=''AddAColumnTransformer'' "
642 + "newColumnName=''countryAdded_s'' newColumnValue=''country_added'' "
643 : "");
644 if (countryCached) {
645 sb.append("processor=''SqlEntityProcessor'' cacheImpl=''SortedMapBackedCache'' ");
646 if (useSimpleCaches) {
647 sb.append("query=''SELECT CODE, COUNTRY_NAME FROM COUNTRIES WHERE DELETED != 'Y' AND CODE='${People.COUNTRY_CODE}' ''>\n");
648 } else {
649
650 if(countryZipper){
651 sb.append(random().nextBoolean() ? "cacheKey=''ID'' cacheLookup=''People.ID'' "
652 : "where=''ID=People.ID'' ");
653 sb.append("join=''zipper'' query=''SELECT PEOPLE.ID, CODE, COUNTRY_NAME FROM COUNTRIES"
654 + " JOIN PEOPLE ON COUNTRIES.CODE=PEOPLE.COUNTRY_CODE "
655 + "WHERE PEOPLE.DELETED != 'Y' ORDER BY PEOPLE.ID "+
656 (wrongCountryOrder ? " DESC":"")
657 + "'' ");
658 }else{
659 sb.append(random().nextBoolean() ? "cacheKey=''CODE'' cacheLookup=''People.COUNTRY_CODE'' "
660 : "where=''CODE=People.COUNTRY_CODE'' ");
661 sb.append("query=''SELECT CODE, COUNTRY_NAME FROM COUNTRIES'' ");
662 }
663 sb.append("> \n");
664 }
665 } else {
666 sb.append("processor=''SqlEntityProcessor'' query=''SELECT CODE, COUNTRY_NAME FROM COUNTRIES WHERE DELETED != 'Y' AND CODE='${People.COUNTRY_CODE}' '' ");
667 sb.append(deltaQueriesCountryTable());
668 sb.append("> \n");
669 }
670 sb.append("<field column=''CODE'' name=''COUNTRY_CODE_s'' /> \n");
671 sb.append("<field column=''COUNTRY_NAME'' name=''COUNTRY_NAME_s'' /> \n");
672 sb.append("</entity> \n");
673 }
674 if (sportsEntity) {
675 sb.append("<entity name=''Sports'' ");
676 sb.append("dataSource=''" + ds + "'' ");
677 sb.append(sportsTransformer ? "transformer=''AddAColumnTransformer'' "
678 + "newColumnName=''sportsAdded_s'' newColumnValue=''sport_added'' "
679 : "");
680 if (sportsCached) {
681 sb.append("processor=''SqlEntityProcessor'' cacheImpl=''SortedMapBackedCache'' ");
682 if (useSimpleCaches) {
683 sb.append("query=''SELECT ID, SPORT_NAME FROM PEOPLE_SPORTS WHERE DELETED != 'Y' AND PERSON_ID=${People.ID} ORDER BY ID'' ");
684 } else {
685 sb.append(random().nextBoolean() ? "cacheKey=''PERSON_ID'' cacheLookup=''People.ID'' "
686 : "where=''PERSON_ID=People.ID'' ");
687 if(sportsZipper){
688 sb.append("join=''zipper'' query=''SELECT ID, PERSON_ID, SPORT_NAME FROM PEOPLE_SPORTS ORDER BY PERSON_ID"
689 + (wrongSportsOrder?" DESC" : "")+
690 "'' ");
691 }
692 else{
693 sb.append("query=''SELECT ID, PERSON_ID, SPORT_NAME FROM PEOPLE_SPORTS ORDER BY ID'' ");
694 }
695 }
696 } else {
697 sb.append("processor=''SqlEntityProcessor'' query=''SELECT ID, SPORT_NAME FROM PEOPLE_SPORTS WHERE DELETED != 'Y' AND PERSON_ID=${People.ID} ORDER BY ID'' ");
698 }
699 sb.append("> \n");
700 sb.append("<field column=''SPORT_NAME'' name=''SPORT_NAME_mult_s'' /> \n");
701 sb.append("<field column=''id'' name=''SPORT_ID_mult_s'' /> \n");
702 sb.append("</entity> \n");
703 }
704
705 sb.append("</entity> \n");
706 sb.append("</document> \n");
707 sb.append("</dataConfig> \n");
708 String config = sb.toString().replaceAll("[']{2}", "\"");
709 log.debug(config);
710 return config;
711 }
712
713 @SuppressForbidden(reason = "Needs currentTimeMillis to set change time for SQL query")
714 @Override
715 protected void populateData(Connection conn) throws Exception {
716 Statement s = null;
717 PreparedStatement ps = null;
718 Timestamp theTime = new Timestamp(System.currentTimeMillis() - 10000);
719 try {
720 s = conn.createStatement();
721 s.executeUpdate("create table countries(code varchar(3) not null primary key, country_name varchar(50), deleted char(1) default 'N', last_modified timestamp not null)");
722 s.executeUpdate("create table people(id int not null primary key, name varchar(50), country_code char(2), deleted char(1) default 'N', last_modified timestamp not null)");
723 s.executeUpdate("create table people_sports(id int not null primary key, person_id int, sport_name varchar(50), deleted char(1) default 'N', last_modified timestamp not null)");
724 log.debug("INSERTING DB DATA USING TIMESTAMP: "
725 + new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ", Locale.ROOT)
726 .format(theTime));
727 ps = conn
728 .prepareStatement("insert into countries (code, country_name, last_modified) values (?,?,?)");
729 for (String[] country : countries) {
730 ps.setString(1, country[0]);
731 ps.setString(2, country[1]);
732 ps.setTimestamp(3, theTime);
733 Assert.assertEquals(1, ps.executeUpdate());
734 }
735 ps.close();
736
737 ps = conn
738 .prepareStatement("insert into people (id, name, country_code, last_modified) values (?,?,?,?)");
739 for (Object[] person : people) {
740 ps.setInt(1, (Integer) person[0]);
741 ps.setString(2, (String) person[1]);
742 ps.setString(3, (String) person[2]);
743 ps.setTimestamp(4, theTime);
744 Assert.assertEquals(1, ps.executeUpdate());
745 }
746 ps.close();
747
748 ps = conn
749 .prepareStatement("insert into people_sports (id, person_id, sport_name, last_modified) values (?,?,?,?)");
750 for (Object[] sport : people_sports) {
751 ps.setInt(1, (Integer) sport[0]);
752 ps.setInt(2, (Integer) sport[1]);
753 ps.setString(3, (String) sport[2]);
754 ps.setTimestamp(4, theTime);
755 Assert.assertEquals(1, ps.executeUpdate());
756 }
757 ps.close();
758 conn.commit();
759 conn.close();
760 } catch (Exception e) {
761 throw e;
762 } finally {
763 try {
764 ps.close();
765 } catch (Exception ex) {}
766 try {
767 s.close();
768 } catch (Exception ex) {}
769 try {
770 conn.close();
771 } catch (Exception ex) {}
772 }
773 }
774 public static final String[][] countries = {
775 {"NA", "Namibia"},
776 {"NC", "New Caledonia"},
777 {"NE", "Niger"},
778 {"NF", "Norfolk Island"},
779 {"NG", "Nigeria"},
780 {"NI", "Nicaragua"},
781 {"NL", "Netherlands"},
782 {"NO", "Norway"},
783 {"NP", "Nepal"},
784 {"NR", "Nauru"},
785 {"NU", "Niue"},
786 {"NZ", "New Zealand"}
787 };
788
789 public static final Object[][] people = {
790 {1,"Jacob","NZ"},
791 {2,"Ethan","NU"},
792 {3,"Michael","NR"},
793 {4,"Jayden","NP"},
794 {5,"William","NO"},
795 {6,"Alexander","NL"},
796 {7,"Noah","NI"},
797 {8,"Daniel","NG"},
798 {9,"Aiden","NF"},
799
800 {21,"Anthony","NE"},
801
802 {11,"Emma","NL"},
803 {12,"Grace","NI"},
804 {13,"Hailey","NG"},
805 {14,"Isabella","NF"},
806 {15,"Lily","NE"},
807 {16,"Madison","NC"},
808 {17,"Mia","NA"},
809 {18,"Natalie","NZ"},
810 {19,"Olivia","NU"},
811 {20,"Samantha","NR"}
812 };
813
814 public static final Object[][] people_sports = {
815 {100, 1, "Swimming"},
816 {200, 2, "Triathlon"},
817 {300, 3, "Water polo"},
818 {310, 3, "Underwater rugby"},
819 {320, 3, "Kayaking"},
820 {400, 4, "Snorkeling"},
821 {500, 5, "Synchronized diving"},
822 {600, 6, "Underwater rugby"},
823 {700, 7, "Boating"},
824 {800, 8, "Bodyboarding"},
825 {900, 9, "Canoeing"},
826
827 {1000, 10, "No Fishing"},
828
829
830 {1100, 11, "Jet Ski"},
831 {1110, 11, "Rowing"},
832 {1120, 11, "Sailing"},
833 {1200, 12, "Kayaking"},
834 {1210, 12, "Canoeing"},
835 {1300, 13, "Kite surfing"},
836 {1400, 14, "Parasailing"},
837 {1500, 15, "Rafting"},
838
839 {1700, 17, "Sailing"},
840 {1800, 18, "White Water Rafting"},
841 {1900, 19, "Water skiing"},
842 {2000, 20, "Windsurfing"},
843 {2100, 21, "Concrete diving"},
844 {2110, 21, "Bubble rugby"}
845 };
846 }